package com.example.swim.Service;


import com.example.swim.Dao.CoachDao;
import com.example.swim.Pojo.Coach;


import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.cache.annotation.CacheEvict;
import org.springframework.cache.annotation.CachePut;
import org.springframework.cache.annotation.Cacheable;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import java.io.InputStream;
import java.util.Iterator;
import java.util.List;

/**
 * @author LiYa
 * @create 2022-07-14 9:44
 */
@Service
public class CoachService {
    @Autowired
    private CoachDao coachDao;

    @Cacheable(value = "allCoach",key = "'findCoach'")
    public List<Coach> findAll()
    {
        return coachDao.findAll();
    }
    @Cacheable(value = "allCoach",key = "#p0")
    public Coach findCoach(int id)
    {
        return coachDao.findById(id);
    }
    @CachePut(value = "allCoach",key = "'findCoach'")
    public List<Coach> insertCoach(Coach coach)
    {
        coachDao.insertCoach(coach);
        return coachDao.findAll();
    }
    @CacheEvict(value = "allCoach",allEntries = true)
    public int del(Integer id)
    {
        return coachDao.delCoach(id);
    }
    @CacheEvict(value = "allCoach",key = "'findCoach'")
    public List<Coach> updateCoach(Coach coach)
    {
        coachDao.updateCoach(coach);
        return coachDao.findAll();
    }
//    验证登录
    public Coach findCoach(Coach coach)
    {
        return coachDao.findCoach(coach);
    }
    @CachePut(value = "allCoach",key = "'findCoach'")
    public List<Coach> importExcel(MultipartFile file)
    {
        InputStream in=null;
        try
        {
//            得到Excel文件的输入流
            in= file.getInputStream();
//            获取excel文件对象
            Workbook workbook=null;
//            根据后缀得到不同的Workbook子类，即HSSFWorkbook或XSSFWorkbook
            if(file.getOriginalFilename().endsWith("xlsx"))
            {
                workbook=new XSSFWorkbook(in);
            }
            else if(file.getOriginalFilename().endsWith("xls"))
            {
                workbook=new HSSFWorkbook(in);
            }
            else
            {
                throw new Exception("文件格式不对或者文件损坏");
            }
//            获得sheet对应的对象，获取第一页对象
            Sheet sheet=workbook.getSheetAt(0);
//            创建Station对象容器
            Coach coach;
//            解析sheet，获得多行数据，并放入迭代器中
            Iterator<Row> ito=sheet.iterator();
            int count=0;
            int sum=0;
            Row row=null;
//            每次导入就把上次的删除
//            delInterfaceList();
            while(ito.hasNext())
            {
                row=ito.next();
//                由于第一行是标题，因此这里需要单独处理
                if(count==0)
                {
                    ++count;
                    continue;
                }
                else
                {
                    if(row!=null)
                    {
                        String name=ExcelUtils.getCellValue((Cell) row.getCell(0));
                        int age=Integer.parseInt(ExcelUtils.getCellValue((Cell) row.getCell(1)));
                        String phone=ExcelUtils.getCellValue((Cell) row.getCell(2));
                        String introduce=ExcelUtils.getCellValue((Cell) row.getCell(3));
                        String skill=ExcelUtils.getCellValue((Cell) row.getCell(4));
                        String image=ExcelUtils.getCellValue((Cell) row.getCell(5));
                        String password=ExcelUtils.getCellValue((Cell) row.getCell(6));
                        coach=new Coach(name,age,phone,introduce,skill,image,password);
                        System.out.println(coach.toString());
                        insertCoach(coach);
                    }
                }
            }
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
        return coachDao.findAll();
    }

}
